package com.crudpro.javacrud.service;

import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.crudpro.javacrud.model.ColumnDefinition;
import com.crudpro.javacrud.model.TableDefinition;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.*;

@Service
@RequiredArgsConstructor
public class DynamicTableService {

    private final JdbcTemplate jdbcTemplate;
    private final DataSource dataSource;

    @Transactional
    public void createTable(String tableName, Map<String, ColumnDefinition> columns) {
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + tableName + " (");
        
        // Add ID column by default
        sql.append("id INT AUTO_INCREMENT PRIMARY KEY, ");
        
        // Add columns from definition
        for (Map.Entry<String, ColumnDefinition> entry : columns.entrySet()) {
            String columnName = entry.getKey();
            ColumnDefinition column = entry.getValue();
            
            sql.append(columnName).append(" ");
            
            // Map type
            switch (column.getType().toLowerCase()) {
                case "string":
                    int maxLength = column.getMaxLength() != null ? column.getMaxLength() : 255;
                    sql.append("VARCHAR(").append(maxLength).append(")");
                    break;
                case "text":
                    sql.append("TEXT");
                    break;
                case "integer":
                case "int":
                    sql.append("INT");
                    break;
                case "decimal":
                case "float":
                case "double":
                    sql.append("DECIMAL(10,2)");
                    break;
                case "boolean":
                case "bool":
                    sql.append("BOOLEAN");
                    break;
                case "date":
                    sql.append("DATE");
                    break;
                case "time":
                    sql.append("TIME");
                    break;
                case "datetime":
                case "timestamp":
                    sql.append("TIMESTAMP");
                    break;
                case "json":
                    sql.append("JSON");
                    break;
                default:
                    sql.append("VARCHAR(255)");
            }
            
            // Required
            if (column.isRequired()) {
                sql.append(" NOT NULL");
            }
            
            // Default value
            if (column.getDefaultValue() != null && !column.getDefaultValue().isEmpty()) {
                sql.append(" DEFAULT '").append(column.getDefaultValue()).append("'");
            }
            
            // Comment
            if (column.getComment() != null && !column.getComment().isEmpty()) {
                sql.append(" COMMENT '").append(column.getComment()).append("'");
            }
            
            sql.append(", ");
        }
        
        // Add created_at, updated_at, deleted_at columns
        sql.append("created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ");
        sql.append("updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ");
        sql.append("deleted_at TIMESTAMP NULL");
        
        sql.append(")");
        
        jdbcTemplate.execute(sql.toString());
    }

    public List<String> getAllTables() {
        List<String> tables = new ArrayList<>();
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            throw new RuntimeException("Error getting tables", e);
        }
        return tables;
    }

    public TableDefinition getTableStructure(String tableName) {
        TableDefinition tableDefinition = new TableDefinition();
        tableDefinition.setTableName(tableName);
        Map<String, ColumnDefinition> columns = new HashMap<>();
        
        try (Connection conn = dataSource.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getColumns(null, null, tableName, null);
            
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String dataType = rs.getString("TYPE_NAME");
                String isNullable = rs.getString("IS_NULLABLE");
                String columnDef = rs.getString("COLUMN_DEF");
                String remarks = rs.getString("REMARKS");
                
                ColumnDefinition columnDefinition = new ColumnDefinition();
                columnDefinition.setType(mapSqlTypeToApiType(dataType));
                columnDefinition.setRequired("NO".equals(isNullable));
                columnDefinition.setDefaultValue(columnDef);
                columnDefinition.setComment(remarks);
                
                columns.put(columnName, columnDefinition);
            }
            
            tableDefinition.setColumns(columns);
        } catch (Exception e) {
            throw new RuntimeException("Error getting table structure", e);
        }
        
        return tableDefinition;
    }

    @Transactional
    public void dropTable(String tableName) {
        jdbcTemplate.execute("DROP TABLE IF EXISTS " + tableName);
    }

    @Transactional
    public void renameTable(String oldName, String newName) {
        jdbcTemplate.execute("RENAME TABLE " + oldName + " TO " + newName);
    }

    @Transactional
    public void addColumn(String tableName, String columnName, ColumnDefinition columnDefinition) {
        StringBuilder sql = new StringBuilder("ALTER TABLE " + tableName + " ADD COLUMN " + columnName + " ");
        
        // Map type
        switch (columnDefinition.getType().toLowerCase()) {
            case "string":
                int maxLength = columnDefinition.getMaxLength() != null ? columnDefinition.getMaxLength() : 255;
                sql.append("VARCHAR(").append(maxLength).append(")");
                break;
            case "text":
                sql.append("TEXT");
                break;
            case "integer":
            case "int":
                sql.append("INT");
                break;
            case "decimal":
            case "float":
            case "double":
                sql.append("DECIMAL(10,2)");
                break;
            case "boolean":
            case "bool":
                sql.append("BOOLEAN");
                break;
            case "date":
                sql.append("DATE");
                break;
            case "time":
                sql.append("TIME");
                break;
            case "datetime":
            case "timestamp":
                sql.append("TIMESTAMP");
                break;
            case "json":
                sql.append("JSON");
                break;
            default:
                sql.append("VARCHAR(255)");
        }
        
        // Required
        if (columnDefinition.isRequired()) {
            sql.append(" NOT NULL");
        }
        
        // Default value
        if (columnDefinition.getDefaultValue() != null && !columnDefinition.getDefaultValue().isEmpty()) {
            sql.append(" DEFAULT '").append(columnDefinition.getDefaultValue()).append("'");
        }
        
        // Comment
        if (columnDefinition.getComment() != null && !columnDefinition.getComment().isEmpty()) {
            sql.append(" COMMENT '").append(columnDefinition.getComment()).append("'");
        }
        
        jdbcTemplate.execute(sql.toString());
    }

    @Transactional
    public void modifyColumn(String tableName, String columnName, String newColumnName, ColumnDefinition columnDefinition) {
        StringBuilder sql = new StringBuilder("ALTER TABLE " + tableName + " CHANGE COLUMN " + columnName + " " + newColumnName + " ");
        
        // Map type
        switch (columnDefinition.getType().toLowerCase()) {
            case "string":
                int maxLength = columnDefinition.getMaxLength() != null ? columnDefinition.getMaxLength() : 255;
                sql.append("VARCHAR(").append(maxLength).append(")");
                break;
            case "text":
                sql.append("TEXT");
                break;
            case "integer":
            case "int":
                sql.append("INT");
                break;
            case "decimal":
            case "float":
            case "double":
                sql.append("DECIMAL(10,2)");
                break;
            case "boolean":
            case "bool":
                sql.append("BOOLEAN");
                break;
            case "date":
                sql.append("DATE");
                break;
            case "time":
                sql.append("TIME");
                break;
            case "datetime":
            case "timestamp":
                sql.append("TIMESTAMP");
                break;
            case "json":
                sql.append("JSON");
                break;
            default:
                sql.append("VARCHAR(255)");
        }
        
        // Required
        if (columnDefinition.isRequired()) {
            sql.append(" NOT NULL");
        } else {
            sql.append(" NULL");
        }
        
        // Default value
        if (columnDefinition.getDefaultValue() != null && !columnDefinition.getDefaultValue().isEmpty()) {
            sql.append(" DEFAULT '").append(columnDefinition.getDefaultValue()).append("'");
        }
        
        // Comment
        if (columnDefinition.getComment() != null && !columnDefinition.getComment().isEmpty()) {
            sql.append(" COMMENT '").append(columnDefinition.getComment()).append("'");
        }
        
        jdbcTemplate.execute(sql.toString());
    }

    @Transactional
    public void dropColumn(String tableName, String columnName) {
        jdbcTemplate.execute("ALTER TABLE " + tableName + " DROP COLUMN " + columnName);
    }

    @Transactional
    public Map<String, Object> insertRecord(String tableName, Map<String, Object> record) {
        // Build column names and placeholders
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        List<Object> values = new ArrayList<>();
        
        for (Map.Entry<String, Object> entry : record.entrySet()) {
            if (columns.length() > 0) {
                columns.append(", ");
                placeholders.append(", ");
            }
            columns.append(entry.getKey());
            placeholders.append("?");
            values.add(entry.getValue());
        }
        
        String sql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders + ")";
        jdbcTemplate.update(sql, values.toArray());
        
        // Get the last inserted ID
        Long id = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Long.class);
        
        // Return the inserted record with ID
        return getRecordById(tableName, id);
    }

    public List<Map<String, Object>> getAllRecords(String tableName) {
        String sql = "SELECT * FROM " + tableName + " WHERE deleted_at IS NULL";
        return jdbcTemplate.queryForList(sql);
    }

    public Map<String, Object> getRecordById(String tableName, Long id) {
        String sql = "SELECT * FROM " + tableName + " WHERE id = ? AND deleted_at IS NULL";
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, id);
        
        if (results.isEmpty()) {
            return null;
        }
        
        return results.get(0);
    }

    @Transactional
    public Map<String, Object> updateRecord(String tableName, Long id, Map<String, Object> record) {
        // Build SET clause
        StringBuilder setClause = new StringBuilder();
        List<Object> values = new ArrayList<>();
        
        for (Map.Entry<String, Object> entry : record.entrySet()) {
            if (setClause.length() > 0) {
                setClause.append(", ");
            }
            setClause.append(entry.getKey()).append(" = ?");
            values.add(entry.getValue());
        }
        
        values.add(id);
        
        String sql = "UPDATE " + tableName + " SET " + setClause + " WHERE id = ?";
        jdbcTemplate.update(sql, values.toArray());
        
        // Return the updated record
        return getRecordById(tableName, id);
    }

    @Transactional
    public void deleteRecord(String tableName, Long id) {
        // Soft delete
        String sql = "UPDATE " + tableName + " SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }

    private String mapSqlTypeToApiType(String sqlType) {
        sqlType = sqlType.toUpperCase();
        if (sqlType.contains("VARCHAR") || sqlType.contains("CHAR")) {
            return "string";
        } else if (sqlType.contains("TEXT")) {
            return "text";
        } else if (sqlType.contains("INT")) {
            return "integer";
        } else if (sqlType.contains("DECIMAL") || sqlType.contains("FLOAT") || sqlType.contains("DOUBLE")) {
            return "decimal";
        } else if (sqlType.contains("BOOL")) {
            return "boolean";
        } else if (sqlType.equals("DATE")) {
            return "date";
        } else if (sqlType.equals("TIME")) {
            return "time";
        } else if (sqlType.contains("TIMESTAMP") || sqlType.contains("DATETIME")) {
            return "timestamp";
        } else if (sqlType.contains("JSON")) {
            return "json";
        } else {
            return "string";
        }
    }
} 